SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 CREATE PROCEDURE [dbo].[LegalFilings]
 --@FundID VARCHAR(10),
 --@StartDate DATETIME, @EndDate DATETIME,
      @ReportName VARCHAR(50)
 AS
      BEGIN
   --FORMAT_OFF

           --DECLARE @fmtonlyON BIT
           --SET @fmtonlyON = 0
           --IF(1 = 0 )
           --BEGIN
           --     SET @fmtonlyON = 1
           --END
           --SET FMTONLY OFF-- FMT
   --FORMAT_ON

  --FORMAT_OFF
  DECLARE @randomDate DATETIME
      , @QStart      DATETIME
      , @QEnd        DATETIME
 DECLARE @exportID   INT
 DECLARE @Date DATETIME = GETDATE()
 SET @randomDate = @date
 
 DECLARE @outp decimal(38,10) 
 
 SET @Qstart = DATEADD(q, DATEDIFF(q, 0, @randomDate), 0)
 SET @QEnd   = DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @randomDate) + 1, 0))
           DECLARE @FundID        VARCHAR(10) ='FSIC'
           DECLARE @StartDate     DATETIME    =@QStart
           DECLARE @EndDate       DATETIME    =@QEnd
           DECLARE @StartExportId INT         = 
		   
(
          SELECT   TOP 1 ExportId
          FROM     FSFundModel_VALCOMM.Reporting.Export e
          WHERE    e.NAVDate >= @QEnd
          AND
                   e.FundId = @FundID
          ORDER BY e.ExportId DESC
           )
           DECLARE @EndExportId INT =
           (
               
          SELECT   TOP 1 ExportId
          FROM     FSFundModel_VALCOMM.Reporting.Export e
          WHERE    e.NAVDate <= @QEnd
          AND
                   e.FundId = @FundID
          ORDER BY e.ExportId DESC
           ) 

		 --  SELECT @StartExportId, @EndExportId

		   DECLARE @QuarterlyPropOriginations DECIMAL(38,10)
		    exec  @QuarterlyPropOriginations = dbo.GetQuarterly_GrossPropOriginationsByFund @FundID = @FundID, -- varchar(50)
		   	@Date = @endDate -- datetime
			DECLARE @QuarterlyPropSales DECIMAL(38,10)
		    exec  @QuarterlyPropSales = dbo.GetQuarterly_GrossPropSalesByFund @FundID = @FundID, -- varchar(50)
		   	@Date = @endDate -- datetime

			--SELECT @QuarterlyPropOriginations
		   

           --IF 1 = 2
           --SELECT NULL AS column1
           --FORMAT_ON --#End SetVariables
           
           IF(@ReportName = 'FundData')
           BEGIN
                SELECT f.EBITDAAvg       AVG_EBITDA
                     , f.MoodysRating    MoodyRating
                     , f.GAAPYldPurchPrc GAAPPurchPrice
                     , f.CompCnt         Count_Companies
                     , f.PurchPrc        WeightAvgPrchPrice
					 , @QuarterlyPropOriginations QuarterlyPropOriginations
					 , @QuarterlyPropSales QuarterlyPropSales
			
                FROM   [FSFundModel_VALCOMM].Reporting.Fund f
				WHERE  f.ExportId = @EndExportId
           END
		IF(@ReportName = 'PropData')
		BEGIN
			      
				SELECT
					COUNT(a.PropReason) PropCount
					--,SUM(a.GAAPYldPurchPrc*a.MktVal)/SUM(a.MktVal) PropWeightYld
				FROM FSFundModel_VALCOMM.Reporting.Asset a
				WHERE
					 a.IsActiveEndOfDay =1 
				AND a.ExportId = @EndExportId 
				AND a.PropReason = 'Proprietary'     
			end 

		--DECLARE @randomDate DATE = GETDATE()
		--DECLARE  @Qstart DATETIME = DATEADD(q, DATEDIFF(q, 0, @randomDate), 0)
		--DECLARE  @QEnd   DATETIME = DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, @randomDate) + 1, 0))
 
 
		SELECT avg(t.TradeAmount) AvgPropCommitment
				
		 FROM   FSFundModel_VALCOMM.Reporting.Trade t

		 INNER JOIN  FSFundModel_VALCOMM.Reporting.Instrument i ON t.InstrumentId = i.InstrumentId 
		 AND t.ExportId = i.ExportId
		 INNER JOIN  FSFundModel_VALCOMM.Reporting.Asset a ON t.ExportId = i.ExportId 
		 AND i.InstrumentId =a.InstrumentId
		 WHERE  
		 t.ExportId = @EndExportId AND 
		 t.TradeDate BETWEEN @QStart AND @QEnd	
		 AND
        t.InstrumentID IN
                          (
                          
                          SELECT DISTINCT(a.InstrumentId)
                          FROM            FSFundModel_VALCOMM.Reporting.Asset a
                          WHERE           a.PropReason = 'Proprietary'
                          AND
                                          a.ExportId = @EndExportId
                          )
 AND
        t.TradeType = 'Purchase'
 AND
        t.ExportId = @EndExportID
   -- Insert statements for procedure here
      END


--EXEC FSFundModel_VALCOMM.Reporting.LegalFilings @ReportName = 'FundData' -- varchar(50)
      

	--   exec  @QuarterlyPropOriginations = FSFundModel_VALCOMM.Reporting.GetQuarterly_GrossPropOriginationsByFund @FundID
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/SEC', 'SCHEMA', N'dbo', 'PROCEDURE', N'LegalFilings', NULL, NULL
GO
